
from playwright_tool import *
from pypinyin import lazy_pinyin
from urllib.parse import urlparse, parse_qs,unquote,quote_plus
from flatten_dict import flatten
from tool import *
from api import fix_excel_xml
import pandas as pd
import openpyxl,csv,json,dirtyjson
import pandas as pd
import re,xlrd




# 定义过滤函数
def filter_strings(input_list):
    result = []
    for s in input_list:
        # 匹配纯字母 或 数字+字母 的字符串，排除纯数字
        if re.search(r'[A-Za-z]', s) and not s.isdigit():
            result.append(s)
    return result

session=requests.session()

def edit_kw0():
    url='https://seller.shopee.co.id/api/pas/v1/product/manual/mass_edit_keyword/'
    headers={
        'cookie':r'_QPWSDCXHZQA=094fd654-ced0-48e3-cded-4263550a4c0c; b-user-id=ed64ce4c-f8f7-ff4c-da80-66abf390d6b1; fulfillment-language=en; _fbp=fb.2.1740624544266.32945435235668388; SPC_F=PEjCwoYDd0DcXv8GXhdW8J64lf0i3QBM; REC_T_ID=68071ef8-f4b5-11ef-a060-367a6c0b10b9; SPC_CLIENTID=UEVqQ3dvWURkMERjhzqjjdgupwwbdqcj; SC_DFP=TgOlMsWXrILYjbgWlaCHCEphnOwwwsOL; REC7iLP4Q=ca870ca7-36ac-433c-9857-441fe3a0861a; SPC_T_IV=ZHVUV3g0YTJjZjhVUFQzNg==; SPC_R_T_ID=LV5FrBdzFoAvnmSwKZXFuxffO3lAyFpFP64+8cPi6C6M70nigwu/U2eUp7YSZfdYFibHBjZaJzCTfgvMfR8jlnmD15Si7pEswLoYdk4h1Ueb5Hg1NEisbhYfKfg3GOAMzR9L1sdHCGuQrFDZhm1hlBI+uNqBk7p37/kLdymcfTo=; SPC_R_T_IV=ZHVUV3g0YTJjZjhVUFQzNg==; SPC_T_ID=LV5FrBdzFoAvnmSwKZXFuxffO3lAyFpFP64+8cPi6C6M70nigwu/U2eUp7YSZfdYFibHBjZaJzCTfgvMfR8jlnmD15Si7pEswLoYdk4h1Ueb5Hg1NEisbhYfKfg3GOAMzR9L1sdHCGuQrFDZhm1hlBI+uNqBk7p37/kLdymcfTo=; SPC_CDS=560da1e0-6577-49f8-877a-f76200f974bf; SPC_SC_SA_TK=; SPC_SC_SA_UD=; SPC_SC_OFFLINE_TOKEN=; SC_SSO=-; SC_SSO_U=-; SPC_SI=2W82aAAAAABTOVFhRlVkNznzQwEAAAAAc205M2NXQ0M=; SPC_SEC_SI=v1-bHR1UjMxRGNZOGNhUmVKVMrzF/peySEgCnayOF3ZbUXqLUcN/I6uOYQM4c6m4nMRiYRTqFK5gT+WKneg2T9Yszjr2ZtmUBJJmtSbF2fJsH8=; _gcl_au=1.1.1288333629.1748483677; SPC_U=-; _gid=GA1.3.294996188.1748483678; SPC_SI=cZQuaAAAAABxM1NZWGJ4cBBTtQAAAAAAc01FazYySzg=; _ga=GA1.1.1541779701.1740624550; SPC_EC=.NFJKbmZBQkxHNU43VXI2aqVbHjn/ZTPCyqYlv+YTeVkE4XfACYaZQqB6ak+0zElWzNK8xNYFf7dWcuAZDGvwMuc0tOmz+v8j848NHrPmpx7q/5s/zGsxkdSf1HC63LRu16gjmGLQRVgA02yygeAs29cDjziiWOJHxEHRRYtnOSFvXUZxGYZe18lypiBthuRnMa5vs3qlZ63IhMnxFZAs5wFk92e363GOnrC7f3j0y5IYgWEcilogzYgPl+83e3hy; SPC_ST=.aXRXUE1UamdWcEY4ZldaYuoasAfF6+ubPmBJYExJAyvLnlHPin46xOmPFutIHLitl5DKlS1xanuBSWh9FOFQQk78uiLpoITDc6soDTNchkHkK0K5WpiH2nm53ET7CXqRitt6Z8P1g7XbRHHn9zKFj1Iem4N/vq2lx40LvLoQ3lPOPPRtram7BKK6NYFkV7ug2KANcHrWehEQoioGvZw7dgLmXzn8V3HAjTHCWHg+fBf3TBIFCJXzPFzHmJmjxehQ; _ga_SW6D8G0HXK=GS2.1.s1748483680$o3$g0$t1748483684$j56$l0$h0; SPC_SC_SESSION=gMTPbT5193DurP/YsdeAhZsqDTzvxGE3Imm5qgfTaE8YfonTx5/YLkiMVZZQ77y4v2uWUEZ7O2556ItO18NV5sDidkaaJpH1V/kCreRSvDncn4p7SDC2Bi0tnwV8p/K5r5Rg+kR3T93Q3+pwI6rKn6LLNzxvsAQk3M5N+UKl9jkQKgWytI2vok0fdte4QnCYsNaOqBdfV+LWZXBM9vvD3awfiJTozRnFhM3fnrsIyKRxjhdBqCOLe8XHu7gB4QpNd_1_1099762151; SPC_STK=UxARDitfm4QxPlbPRDXKJIbQyefr5Wfhoxk6SvxIhM9KpVc7ucuteMpKg2rnZnN1HOnyob8Mu35G7joARZcQLJkuLZjOX5dcea3L2A6+wloNxX6jjxCFk6LgMKRmJLMsDlyHhhbNgyJQrv6PuMx2fmsGGrGgaCFzkRwoyn2S5iKF6mgCvZJIxeDX1R6eDU4LOAsXHO7n21Zsvy9hSe2hxyg3IhirVszbnxBcIJjznr39gvafHEM7nmKWv63G1SEP+0ghqWirYZc7uzVRj30z9nrsQ1J9dI7k9Ctc9e4VNqHBNnoTPmS5maer1UGDanocBLfyM3UyjghXkt54vrGIeApmNwvYPaQ9KKpJGWLjpdxGSlPeDvHTZWz2/8j13Tnp7zMmj+Z13anxX9oiyORmU8QvLFagEl6X6gFLALMefv11kcizDufAA/eA5KT5Ty42kgfkKp/4zVuGemIdlpIs+w==; SPC_CDS_CHAT=272a58e4-03e4-4f35-9a37-41d41451e3da; _sapid=b4dd0120400a4247b180cd220c16f27d8b6c427509221b9811898ef2; CTOKEN=p95cejwyEfCH%2FaJEppXmxQ%3D%3D; shopee_webUnique_ccd=KteccxNMNfJ2AwkbK70gJw%3D%3D%7CYfeg3duMy%2BABabNcLnuAkqr6Q33E37ULL61O1bY7E2NsQxBmJFZKulGy7vv5hNz%2Bd3lsLDRkpC%2BVA2NfGXM%3D%7C1CsUkq%2BKtH%2FIDYm1%7C08%7C3; ds=4db28bb8e8ecd18780c24bf209fc1617',
        'user-agent':'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/125.0.0.0 Safari/537.36'
    }

    params={
        'SPC_CDS':'560da1e0-6577-49f8-877a-f76200f974bfd',
        'SPC_CDS_VER': 2
    }

    data1= {
            "campaign_id": 266526797, 
            "keyword_edit_list": [
                {"keyword": "workout streetwear x scribbles", "type": "delete"}], 
            "header": {}
            }

    data2={"campaign_id":266526797,
           "keyword_edit_list":[
               {"keyword":"cctv outdoor wifi 8mp","type":"change_match_type","change_match_type":{"match_type":"exact"}}
            ],
            "header":{}}

    data3={"campaign_id":266526797,"keyword_edit_list":[{"keyword":"hadiah collection","type":"change_bid_price","change_bid_price":
                                                         {"price":300*100000}}],"header":{}}

    data4={"campaign_id":266526797,"keyword_edit_list":[{"keyword":"rpm koso","type":"add","add":{"bid_price":25000000,"match_type":"broad"}}],"header":{}}

    res=session.post(url,params=params,json=data4,headers=headers)
    print(res.json())



    ss='浏览器ID:ks9wqux, 店铺ID:志伟阿里狗-12号,波兰店铺上传,运行结果:10'
    pat=re.compile(r'浏览器ID:(.+?), 店铺ID:.*?波兰店铺上传,运行结果:')
    pat2=re.compile(r'bid:(.+?), 出现错误:')
    with open ('55.txt','r',encoding='utf-8') as f:

        cot=f.read()


    bids_suc=pat.findall(cot)
    bids_fail=pat2.findall(cot)

    rs=tms.ExecQuery('select browserid from houtai_alg where isupload=1')
    cj_bids=[r[0] for r in rs]

    nbids=set(cj_bids) - set(bids_fail+bids_suc)
    print(nbids)

def alg_dd():
    with open('JSONDATA/阿里狗买家纠纷.json','r',encoding='utf-8') as f:

        jsdata=json.load(f)

    ord_data=jsdata['content'][0]

    flat_dict = flatten(ord_data, reducer="underscore",enumerate_types=(list,))
    with open('JSONDATA/阿里狗纠纷展平模式.json','w',encoding='utf-8') as f:
        json.dump(flat_dict,f,ensure_ascii=False,indent=4)

    for k,v in flat_dict.items():
        if isinstance(v,bool):
            sql_type='int'
        elif isinstance(v,str) and is_decimal(v):
            sql_type='decimal(6,2)'
        elif isinstance(v,float):
            sql_type='decimal(6,2)'
        else:
            sql_type='nvarchar(200)'

        print(f'{k} {sql_type} null,')

def read_alg_report():
    fpath=r'C:/Users/Administrator/Desktop/2024/新后台群控/ALReport/测试图片.xlsm'
    df = pd.read_excel(fpath, sheet_name='Template',engine='openpyxl',
                       dtype={'Product ID (EAN/UPC/ISBN/ISSN/Allegro Product ID)': str,'Offer ID':str},header=3)
    df = df.map(lambda x: None if pd.isna(x) else x)

    dict_list = df.to_dict(orient='records')
    rrr=dict_list[0]
    print(rrr)
    fail_des=rrr['Result']
    if '- Missing offer description\n- Missing image' == fail_des:
        print('aaa')

def get_image_link(product_id):
    if pd.isna(product_id):
        return None

    query = "SELECT ZhuTuWangZhi FROM allgoodpro WHERE pean =?"

    rs=tms.ExecQuery(query,(product_id,))
    # image_list=rs[0][0].split('|')
    # gtn_iamge=None
    # for pic in image_list:
    #     if 'EAN-GTIN' in pic:
    #         gtn_iamge=pic
    #         break
    # if not gtn_iamge:
    #     gtn_iamge=image_list[0]
    return rs[0][0]

def get_des_link(product_id):
    if pd.isna(product_id):
        return None

    query = "SELECT biaoti FROM allgoodpro WHERE pean =?"

    rs=tms.ExecQuery(query,(product_id,))
    return rs[0][0]

def full_info_bol_report():

    fpath=r'C:/Users/Administrator/Desktop/2024/新后台群控/ALReport/测试图片.xlsm'
    df = pd.read_excel(fpath, sheet_name='Template',engine='openpyxl',
                    dtype={'Product ID (EAN/UPC/ISBN/ISSN/Allegro Product ID)': str,'Offer ID':str},header=3)

    df['Images'] = df['Product ID (EAN/UPC/ISBN/ISSN/Allegro Product ID)'].apply(get_image_link)
    out_fpath=fpath.replace('.xlsm',f'生成_{getTimeStr()}.xlsx')
    df.to_excel(out_fpath, sheet_name='Template', index=False, engine='openpyxl')

async def get_pbrands():

    browser= await openChrome()
    page=await browser.newPage()

    await repeat_goto(page,'https://allegro.pl/regulamin/zalacznik/1')
    #await asyncio.sleep(5)

    pbrand_txt_xpath='//p[contains(text(),"& Other Stories, 111Skin,")]/text()'
    await wait_one_xpath(page,pbrand_txt_xpath,max_wait=60)

    cot= await page.content()
    pbrnad_txt=etree.HTML(cot).xpath(pbrand_txt_xpath)[0]
    pbrands=pbrnad_txt.split(',')
    pbrands=[ppp.strip() for ppp in pbrands]
    pbrands[-1]=pbrands[-1][:-1]
    rs_yqqc=tms.ExecQuery('select count(*) from qqc where qtype=?',('波兰语',))
    yqq_count=rs_yqqc[0][0]
    print(f'本次从官方共采集 {len(pbrands)} 个侵权词')
    print(f'数据库原有波兰语侵权词数量共 {yqq_count} 个,开始更新到数据库...')
    suc=0
    fail=0
    j=1

    for pbrand in pbrands:
        zds=['qcontent','qtype']
        params=[pbrand,'波兰语']

        aff=tms.merge_into('qqc',zds,params,['qcontent'],True)
        if aff:
            print(f'词《{pbrand}》,更新到词库成功,剩余需更新{len(pbrands)-j}')
            suc+=1
        else:
            print(f'词《{pbrand}》,更新到词库失败,剩余需更新{len(pbrands)-j}')
            fail+=1
        j+=1
    rs_now_qqc=tms.ExecQuery('select count(*) from qqc where qtype=?',('波兰语',))
    print(f'本次从官方共采集 {len(pbrands)} 个侵权词,更新成功 {suc} 个,更新失败 {fail} 个')

    print(f'更新前波兰侵权词库:{yqq_count} 个,更新后: {rs_now_qqc[0][0]} 个')
    
    
def dosql():
    sqlstr='''WITH cte AS (
        SELECT DISTINCT TOP 100 itemid  
        FROM proshopeeph WITH (ROWLOCK, UPDLOCK, READPAST) 
        WHERE pmbid=9753
        AND yntitle IS NOT NULL 
        AND daochu = 0
        )
        UPDATE proshopeeph
        SET daochu = 100
        OUTPUT inserted.itemid
        WHERE itemid IN (SELECT itemid FROM cte);'''
    rs=tms.ExeANDQuery(sqlstr)
    itemids=[r[0] for r in rs]
    print(set(itemids))




def upLoad_maijiahao(excel_path):
    '''上传菲律宾买家号'''

    pdatas=getDictDatasByEecel(excel_path)
    print(f'本次共 {len(pdatas)} 个菲律宾买家号待导入')
    j=1
    for pdata in pdatas:
        bid=pdata['ID']
        if not bid:
            continue
        binfo=get_SunBrowser_info(bid)
        print(f'浏览器:{bid},成功获取环境信息:{binfo}')
        gid=binfo['group_id']
        gname=binfo['group_name']
        bname=binfo['name']
        if not bname:
            bname=f'菲律宾买家号{j}_{bid}'
            update_SunBrowser_info({'user_id':bid,'name':bname})

        ipstr=binfo['user_proxy_config']['proxy_host']
        port=binfo['user_proxy_config']['proxy_host']
        proxy_type=binfo['user_proxy_config']['proxy_type']
        porxy_uid=binfo['user_proxy_config']['proxy_user']
        proxy_pwd=binfo['user_proxy_config']['proxy_password']
        pdict={
            'BrowserID':bid,
            'DpName':bname,
            'GroupID':gid,
            'GroupName':gname,
            'UserName':str(pdata['卖家账号']).strip(),
            'Password':str(pdata['密码']).strip(),
            'ProxyIP':f'{proxy_type}://{porxy_uid}:{proxy_pwd}@{ipstr}:{port}'

        }

        zds=list(pdict.keys())
        paras=list(pdict.values())

        aff=tms.merge_into('ShopeePHAccount',zds,paras,['BrowserID'],True)

        print(f'存入浏览器:{bid},信息状态:{aff}')
        j+=1

def ppppp(pdata,pitem=[1,2,3]):

    pid,purl,itemid=pitem
    is_update_pro=False
    is_update_sku=False

    pdata_item=pdata['item']
    fe_categories=json.dumps(pdata_item['fe_categories'])
    cmt_count=pdata['product_review']['cmt_count']
    attributes=json.dumps(pdata['product_attributes'])
    tier_variations=pdata_item['tier_variations']
    sku_info={
        'sku1_name':None,
        'sku2_name':None,
        'sku1_options':None,
        'sku2_options':None,
        'sku1_imgs':None,
        'sku2_imgs':None,
    }
    for j in range(1,len(tier_variations)+1):
        vari=tier_variations[j-1]
        sku_info[f'sku{j}_name']=vari['name']
        sku_info[f'sku{j}_options']=vari['options']
        if vari.get('images'):
            sku_info[f'sku{j}_imgs']=[f'https://down-ph.img.susercontent.com/file/{img}' for img in vari['images']]
    print(f'sku_info:{sku_info}')
    pbrand=pdata_item['brand']
    pdes=pdata_item['description']

    #补全表《ShopeePHADPro》信息
    models=pdata_item['models']
    etra_dict={
        'fe_categories':fe_categories,
        'cmt_count':cmt_count,
        'attributes':attributes,
        'sku1_name':sku_info['sku1_name'],
        'sku2_name':sku_info['sku2_name'],
        'models':json.dumps(models),
        'pbrand':pbrand,
        'pdec':pdes
    }


    #补全SKU信息
    pskus=[]
    now_time=datetime.datetime.now()
    aff_sku=0
    for moddd in models:
        skuid=moddd['model_id']
        sku_price=int(moddd['price']/100000)
        sku_text=moddd['name']
        sku_sold=moddd['sold']
        tier_index=moddd['extinfo']['tier_index']

        skuddd={
            'sku1':None,
            'sku2':None,
            'sku1_img':None,
            'sku2_img':None
        }
        print(tier_index)
        for tj in range(len(tier_index)):
            tind=tier_index[tj]
            print(sku_info[f'sku{tj+1}_options'][tind])
            skuddd[f'sku{tj+1}']=sku_info[f'sku{tj+1}_options'][tind]
            if sku_info[f'sku{tj+1}_imgs']:
                skuddd[f'sku{tj+1}_img']=sku_info[f'sku{tj+1}_imgs'][tind]
        print(skuddd)
        psku_dict={
            'itemid':itemid,
            'skuid':skuid,
            'sku_text':sku_text,
            'sku1':skuddd['sku1'],
            'sku2':skuddd['sku2'],
            'sku1_img':skuddd['sku1_img'],
            'sku2_img':skuddd['sku2_img'],
            'sku_price':sku_price,
            'sku_sold':sku_sold,
            'from_pid':pid,
            'update_time':now_time,
            'upload_time':now_time
        }

        print(psku_dict)
        break

def parse_1688_html(cot):
    
    mat_des=re.search(r'"result":(.*?)"module":',cot,re.S)
    if mat_des:
        json_txt= mat_des.group(1)
        return dirtyjson.loads(json_txt)

def set_no_pod():
    sjms= [
    "KTE99yoW", "6mЗZKAbV", "M0PLvD6M", "ZyidKKz1", "PGgENVwI", "CTLAX8ye", "KrjzcNon", "DhvOkVvh", 
    "YRdhDtte", "K9wWMfrh", "RgWD3PoY", "kOUnUKjR", "dSbzRwcS", "z4aUcvr0", "UjQLmsMA", "1FlhYvnl", 
    "ADUHEu3I", "WXfRmZ37", "4NaxvkbX", "3Ok5BbNi", "wu9gb845", "uKU2llDr", "argM3DDG", "66a0U95n", 
    "x65rYuD1", "OY1cLVKB", "bnWRuzFW", "1i8KUЗB9", "5JlmH4Gl", "ZH8ОfbUB", "jgv1MBNs", "njffwmwO", 
    "HA724L8G", "qD094Jae", "cUI0qEJl", "mySwvGZW", "muo2L4IB", "apzx2Lhx", "a4icqmKx", "I5Z2zLJr", 
    "WWS4pz5B", "7k2LUxiA", "WрYiPyuJ", "bR3VSevi", "924fZWQ8", "R4JfzATx", "cKY0ajbО", "QJd9DxOG", 
    "ssFoUOVA", "eo7MLFhP", "0G3qLtLc", "5G2UWAjx", "qLeoNy1W", "p2xбрBow", "7Bnlt94e", "S4duLDRU", 
    "eJUrP0Dl", "FVeCf8IY", "ph6zIndX", "UiYQHhI3", "74PpWWMC", "7fIx0NU1", "aWQiKAwf", "V7ROjPfK", 
    "4xJmcacD", "hAMcRBYK", "yQwMmj75", "ruu2522C", "7CTTtA29", "bJXzQ6hC", "rKzWZHfj", "FUGPgWEL", 
    "jm07mz33", "thiWyYLi", "5i7QdQdf", "PWVz98bU", "ZbsjliBn", "IYSEdPTJ", "5WdsJhtT", "5SI0uAIQ", 
    "Tj00y9Pq", "Qf323hBz", "dwEbUp9g", "jQJh6Pap", "JwH6ZdWz", "4H8JZKCZ", "uo6ViP8e", "H53UdzsY", 
    "aEHwSFa9", "UJMjf7ac", "42vuoIYp", "2936JTmC", "bMILKEgv", "lqeY8pac", "QqIFDtCT", "JMsfY7RG", 
    "H2PDs2H0", "kGROgnix", "20RH4ulw", "f7SEGETV", "lsCDIUpH", "iBY1HnQo", "7zo2S8yc", "7D9NbBMJ", 
    "Y8wjzlyH", "3yoydrMG", "3RgYtuQi", "xboGxNo0", "DQafhyJK", "cPh8zbQF", "rhovbaVv", "BKZSj37d", 
    "0pifyNqP", "SZKOLXZr", "0P3JGEZ9", "oT16tKRn", "3jewTlwf", "2NgY2PyR", "K8Oz05vf", "sveuhws9", 
    "wfnElXb6", "04S5XyGU", "JNFQS2Nq", "CSsDZZwo", "U6M1OjIw", "WBJDuoWg", "JiTKcVZb", "2KKHB1JU", 
    "WsMj03G5", "yzuqpwtb", "QWF7JAud", "6Nq4f1EJ", "fKvrGZWZ", "ugdptP6t", "HPbNnqsA", "74XuMxQp", 
    "bLaI1NRL", "ZAiRJSel", "sv40JBeV", "ZebzLEtg", "LrEmSOYF", "07N93ISA", "zEbrgpn2", "IKXO4V1X", 
    "7CEVqG7b", "4H3AIOLC", "euQvYgxk", "gifH9Ehm", "WxFCN7PJ", "oh6jiu9H", "u1186leA", "jbwkd0Ag", 
    "N9ot8PYr", "2T93xywI", "I9UQWTNH", "7sKzSri1", "LdwTltZz", "WODTPVFj", "F1xoW7r0", "D75rL5sF", 
    "bSUyuELq", "N5ICK5uA", "7MoLZ6ub", "mKcThwBt", "djf0WoyE", "7LC4nOFT", "mDk5RSWQ", "Kc6kK8sC", 
    "hrS0XKNM", "N8w9Gstp", "U198Sj9b", "ZdXg3iH5", "vBxUiPJt", "Kigkt19p", "ySiXFW2i", "hAMCRBYK", 
    "jGIheIER", "BdXsWvkl", "bfOoNano", "xZNArP0K", "4wnfDeMy", "ZbNJa2VH", "KUkKUhTs", "W45SPmON", 
    "ZpwB68Af", "HIx3TBZV", "CWJORYgL", "SKNd1slu", "Wfpt5qxV", "o25Y4SIb", "yifrkHgw", "J2YOvWzb", 
    "UWARs5dp", "AD5DDGHN", "46VbEgMS", "ZZ3VoLkV", "T1WUN5VB", "eDWcS04d", "NCOxM4kM", "NUIxPWPo", 
    "JBOklqVZ", "CTKb6YFF", "aQIMCMJt", "jhAZeGez", "hFlpks2H", "y21NJgtO", "UUobtFHV", "kQGipsiu", 
    "7Jr6lOUn", "gzIRoKKO", "LFteM7zF", "6dTwWh9S", "Bh9ihDAt", "t4axvUTO", "QK05T8Fu", "qMkoAAy4", 
    "BMfvvJkC", "ea3soW5x", "IFGRjVBq", "n6SMrdM9", "YLU0dnle", "UiYQHhl3", "CMDvePJn", "90IJBhDY", 
    "zXFS87OT", "dxtKSi0v", "7Anz2ng6", "crMjAYpv", "I8WfWwqg", "KdPMcjts", "TC8rk0Qg", "VgEXTzfJ", 
    "sMc2r9td", "WpYiPyuJ", "a3reetkj", "7o1yig0q", "QocJXX0a", "hBFmGAcM", "4kmPxVON", "g4YhOltc", 
    "fyAb0RRJ", "6kG7xpsr", "DbfIyfh0", "D3IHMZyV", "9r7whl7T", "t0qc49j2", "JLfvFtaU", "MbgC90FZ", 
    "CT7HgRr1", "ULyBkYll", "zPQuAx6o", "6XI2aZ1c", "UApljuDk", "pvnvcE7d", "Kqz2TtiW", "tN2GrUGH"
]
    sjm_str=','.join([f"'{sjm}'" for sjm in set(sjms)])
    rs=tms.ExecQuery(f"select * from randomcodes where randomcode in ({sjm_str})")

    itemid_str=','.join(f"'{r[0]}'" for r in rs)

    aff=tms.ExecNoQuery(f"update proshopeeph set ispodcp=-1 where itemid in ({itemid_str})")

    print(aff)


def read_xls_to_dict_list(file_path):
    # 读取 .xls 文件
    df = pd.read_excel(file_path,dtype={'订单编号': str,'Offer ID':str})  # 如果需要指定 sheet，可加 sheet_name 参数
    # 转换为字典列表
    dict_list = df.to_dict(orient='records')
    return dict_list

def update_ynad():
    pdatas=read_xls_to_dict_list('4.29更新.xls')

    suc=0
    fail=0
    ign=0
    j=1
    for pdata in pdatas:
        try:
            itemid=str(pdata['Offer ID']).strip()
            aff=tms.ExecNoQuery('update S1688Pro set is_yn_ad=1 where is_yn_ad=0 and itemid=?',(itemid,))
            if aff>0:
                print(f'第{j}个iteid:{itemid},成功标记:{aff}')
                suc+=1
            else:
                print(f'第{j}个iteid:{itemid},忽略标记:{aff}')
                ign+=1
        except Exception as e:
            traceback.print_exc()
            fail+=1
            print(f'第{j}个iteid:{itemid},出错 =>{e}')
        j+=1

    print(f'成功:{suc},失败:{fail},忽略:{ign}')


def upload_houtai_th():

    pdatas=getDictDatasByEecel('泰国店铺.xlsx')
    pdatas=pdatas[1:]

    for pdata in pdatas:
        bid=pdata['BrowserID']
        bname=pdata['DpName']
        pdata.pop('id')
        pdata['is_KJ']=1 if pdata['Platform'].startswith('https://seller.shopee.cn') else 0

        zds=list(pdata.keys())
        paras=list(pdata.values())

        aff=tms.merge_into('houtai_th',zds,paras,['BrowserID'],True)

        print(f'店铺:《{bname}》,{bid},更新店铺资料状态:{aff}')


def test_jsapi():

    url='https://seller.shopee.co.id/api/pas/v1/report/get/'
    headers={
        'cookie':r'__QPWSDCXHZQA=094fd654-ced0-48e3-cded-4263550a4c0c; b-user-id=ed64ce4c-f8f7-ff4c-da80-66abf390d6b1; fulfillment-language=en; _fbp=fb.2.1740624544266.32945435235668388; SPC_F=PEjCwoYDd0DcXv8GXhdW8J64lf0i3QBM; REC_T_ID=68071ef8-f4b5-11ef-a060-367a6c0b10b9; SPC_CLIENTID=UEVqQ3dvWURkMERjhzqjjdgupwwbdqcj; SC_DFP=TgOlMsWXrILYjbgWlaCHCEphnOwwwsOL; REC7iLP4Q=ca870ca7-36ac-433c-9857-441fe3a0861a; SPC_T_IV=ZHVUV3g0YTJjZjhVUFQzNg==; SPC_R_T_ID=LV5FrBdzFoAvnmSwKZXFuxffO3lAyFpFP64+8cPi6C6M70nigwu/U2eUp7YSZfdYFibHBjZaJzCTfgvMfR8jlnmD15Si7pEswLoYdk4h1Ueb5Hg1NEisbhYfKfg3GOAMzR9L1sdHCGuQrFDZhm1hlBI+uNqBk7p37/kLdymcfTo=; SPC_R_T_IV=ZHVUV3g0YTJjZjhVUFQzNg==; SPC_T_ID=LV5FrBdzFoAvnmSwKZXFuxffO3lAyFpFP64+8cPi6C6M70nigwu/U2eUp7YSZfdYFibHBjZaJzCTfgvMfR8jlnmD15Si7pEswLoYdk4h1Ueb5Hg1NEisbhYfKfg3GOAMzR9L1sdHCGuQrFDZhm1hlBI+uNqBk7p37/kLdymcfTo=; SPC_SI=2W82aAAAAABTOVFhRlVkNznzQwEAAAAAc205M2NXQ0M=; _gcl_au=1.1.1288333629.1748483677; _ga=GA1.1.1541779701.1740624550; SPC_ST=.aXRXUE1UamdWcEY4ZldaYuoasAfF6+ubPmBJYExJAyvLnlHPin46xOmPFutIHLitl5DKlS1xanuBSWh9FOFQQk78uiLpoITDc6soDTNchkHkK0K5WpiH2nm53ET7CXqRitt6Z8P1g7XbRHHn9zKFj1Iem4N/vq2lx40LvLoQ3lPOPPRtram7BKK6NYFkV7ug2KANcHrWehEQoioGvZw7dgLmXzn8V3HAjTHCWHg+fBf3TBIFCJXzPFzHmJmjxehQ; _ga_SW6D8G0HXK=GS2.1.s1748483680$o3$g0$t1748483684$j56$l0$h0; SPC_SC_SESSION=gMTPbT5193DurP/YsdeAhZsqDTzvxGE3Imm5qgfTaE8YfonTx5/YLkiMVZZQ77y4v2uWUEZ7O2556ItO18NV5sDidkaaJpH1V/kCreRSvDncn4p7SDC2Bi0tnwV8p/K5r5Rg+kR3T93Q3+pwI6rKn6LLNzxvsAQk3M5N+UKl9jkQKgWytI2vok0fdte4QnCYsNaOqBdfV+LWZXBM9vvD3awfiJTozRnFhM3fnrsIyKRxjhdBqCOLe8XHu7gB4QpNd_1_1099762151; SPC_STK=UxARDitfm4QxPlbPRDXKJIbQyefr5Wfhoxk6SvxIhM9KpVc7ucuteMpKg2rnZnN1HOnyob8Mu35G7joARZcQLJkuLZjOX5dcea3L2A6+wloNxX6jjxCFk6LgMKRmJLMsDlyHhhbNgyJQrv6PuMx2fmsGGrGgaCFzkRwoyn2S5iKF6mgCvZJIxeDX1R6eDU4LOAsXHO7n21Zsvy9hSe2hxyg3IhirVszbnxBcIJjznr39gvafHEM7nmKWv63G1SEP+0ghqWirYZc7uzVRj30z9nrsQ1J9dI7k9Ctc9e4VNqHBNnoTPmS5maer1UGDanocBLfyM3UyjghXkt54vrGIeApmNwvYPaQ9KKpJGWLjpdxGSlPeDvHTZWz2/8j13Tnp7zMmj+Z13anxX9oiyORmU8QvLFagEl6X6gFLALMefv11kcizDufAA/eA5KT5Ty42kgfkKp/4zVuGemIdlpIs+w==; SPC_U=1099762151; SPC_SI=m5UuaAAAAABNd2liQWFQMyjoMQEAAAAAS01zTUJQNko=; SPC_EC=.UHNzUElCYjh2aW9SRnl2R22g7KodIToMMe2QVqckYFEOQVvj0jEFwlerBFH/mWoDXZ/69ucx1b3sioLnV4pS7Y6sI0SbNpLAL/RuqKPwcPrDVVkW3Y6McsiF+/o07+lXAeLDK36byaH4k5gd5Wdk6QcQ+v/3hC6Ho9egURqSlqgFkH7V6u8WFBY6S329OVDngzThYjA6B4iaigAZ9Gz09AVSi1Y2zX/+2JeHkht8zlT1nzYZobEwYwzZz6av/InW; SPC_SEC_SI=v1-ZU1GUXphYmlrUWlQU1B0dLEMAGiPJtmwlks0sIya++WFApR3MQHpcD3Kq/pu5y7hZDHRI2LyVIjgE9K5p0Gvx9Rs8KyKNPDZ8GFm/OlhEN8=; SPC_CDS=1c364625-f47b-4ac4-9e37-1490a1d5014f; SPC_CDS_CHAT=91cc094c-2e18-4f4d-8c88-46d2e7a85d0f; _sapid=b4dd0120400a4247b180cd220c16f27d8b6c427509221b9811898ef2; CTOKEN=iXCmAD0dEfCH%2FaJEppXmxQ%3D%3D; shopee_webUnique_ccd=LtdePZepXD9q%2FlO9uWWx2Q%3D%3D%7CmPSg3duMy%2BABabNcLnuAkqr6Q33E37ULL61O1bWMX2ZsQxBmJFZKulGy7vv5hNz%2Bd3lsLDRkpC8%3D%7C1CsUkq%2BKtH%2FIDYm1%7C08%7C3; ds=983a67cb00e3d9922a1a5c32224a1754',
        'user-agent':'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/125.0.0.0 Safari/537.36'
    }

    st,ed=get_shopee_timestamp('yesterday')
    params={
        'SPC_CDS':'1c364625-f47b-4ac4-9e37-1490a1d5014f',
        'SPC_CDS_VER': 2
    }

    data={
            "start_time":st,
            "end_time":ed,
            "campaign_type":"product",
            "agg_type":"keyword",
            "filter_params":{"campaign_id":266526797},
            "need_ratio":True,
            "header":{}
        }

    res=requests.post(url,headers=headers,params=params,json=data)

    json_data=res.json()
    with open('测试数据.json','w',encoding='utf-8') as f:

        json.dump(json_data,f,ensure_ascii=False)


def to_camel_pinyin(text: str) -> str:
    """
    将中文字符串转换为驼峰拼音（CamelCase）。
    例如：'你好世界' -> 'NiHaoShiJie'
    """
    pinyins = lazy_pinyin(text)
    return ''.join(word.capitalize() for word in pinyins)



def read_xls_to_dict_list(file_path,dtype=None):
    df = pd.read_excel(file_path,dtype=dtype)
    #df = df.ffill()
    df = df.map(lambda x: None if pd.isna(x) else x)
    #df = df.where(pd.notna(df), None)
    return df.to_dict(orient='records') 


def read_xls_with_merged_cells(file_path):
    wb = xlrd.open_workbook(file_path, formatting_info=True)
    sheet = wb.sheet_by_index(0)

    # 获取合并单元格的范围列表：[(row_start, row_end, col_start, col_end), ...]
    merged_cells = sheet.merged_cells

    data = []

    for row_idx in range(sheet.nrows):
        row_data = {}
        for col_idx in range(sheet.ncols):
            cell_value = sheet.cell_value(row_idx, col_idx)

            # 检查该单元格是否在合并区域中，如果为空则尝试获取合并值
            if cell_value == '':
                for (row_start, row_end, col_start, col_end) in merged_cells:
                    if row_start <= row_idx < row_end and col_start <= col_idx < col_end:
                        cell_value = sheet.cell_value(row_start, col_start)
                        break

            # 你可以使用表头作为 key（第一行）
            key = sheet.cell_value(0, col_idx)
            row_data[key] = cell_value
        if row_idx != 0:  # 跳过标题行
            data.append(row_data)

    return data


def create_table(tb_name:str,pdata:dict):

    sql_str=f'''CREATE TABLE [dbo].[{tb_name}](
                [id] [int] IDENTITY(1,1) primary key NOT NULL,
    '''
    col_list=[]
    json_data={'id':'数据库自增长ID'}
    for k,v in pdata.items():
        
        zd=re.sub(r'\s*\(.*?\)\s*$', '', k).strip()
        zd=zd.replace('买家','Buyer').replace('卖家','Seller').replace('名字','Name').replace('名','Name').replace('时间','Time')
        zd_py=to_camel_pinyin(zd)
        zd_py=zd_py.replace('：','_').replace('、','_').replace(' ','')
        
        if isinstance(v,int):
            col_list.append(f'[{zd_py}] [int] NULL')
        elif isinstance(v,float):
            col_list.append(f'[{zd_py}] [decimal](10,2) NULL')
        elif isinstance(v,pd.Timestamp):
            col_list.append(f'[{zd_py}] [datetime] NULL')
        else:
            col_list.append(f'[{zd_py}] [nvarchar](500) NULL')
        json_data[zd_py]=k
    col_list.append('update_time [datetime] NULL')
    col_list.append('upload_time [datetime] NULL')
    json_data['update_time']='数据更新时间'
    json_data['upload_time']='数据创建时间'
    sql_str+=',\n'.join(col_list)
    sql_str+=') ON [PRIMARY]'
    print(sql_str)
    aff=tms.ExecNoQuery(sql_str)
    print(f'表:《{tb_name}》,创建状态:{aff}')
    with open(f'TABLEJSON/{tb_name}.json','w',encoding='utf-8') as f:
        json.dump(json_data,f,ensure_ascii=False,indent=4)



sss=get_shopee_timestamp()

def mkr():
    

    paras={
        'SPC_CDS': 'f040c141-7be8-408a-8eba-3bda94159813',
        'SPC_CDS_VER': 2
    }
    st,et=get_shopee_timestamp()
    data={  
            "start_time":st,
            "end_time":et,
            "campaign_type":"product",
            "agg_type":"keyword",
            "filter_params":{"campaign_id":266526797},
            "need_ratio":True,
            "header":{}}
    headers={
        'cookie':r'SPC_SC_SA_TK=; SPC_SC_SA_UD=; SPC_SC_OFFLINE_TOKEN=; SC_SSO=-; SC_SSO_U=-; fulfillment-language=en; ca_gen_id=1962924838; SPC_U=1408398178; SPC_F=aWeUtV7zm1XyJ2ydetvJCC2IdhQhooyE; REC_T_ID=27fbbd78-4b19-11f0-bc20-bef0a7f183fa; SPC_R_T_ID=P55s/062HLrk8pl6tOyb6RwOmPk9jVJ2Q09ODuWcpQ5MGKzrSv9z9pBv1tpNiUnY8UYw2Tuz9pTg97Ze2XI0cloJXkkdJkVL+stFqxczr8u/WGMdOHu1fS5MEpdjFV66TQyK7A5z45AoPB6YB1PUkoldvRZfHvZ7mBOxxc0sDZs=; SPC_R_T_IV=c3pUWkdLT1Vqc2JsaEZSQQ==; SPC_T_ID=P55s/062HLrk8pl6tOyb6RwOmPk9jVJ2Q09ODuWcpQ5MGKzrSv9z9pBv1tpNiUnY8UYw2Tuz9pTg97Ze2XI0cloJXkkdJkVL+stFqxczr8u/WGMdOHu1fS5MEpdjFV66TQyK7A5z45AoPB6YB1PUkoldvRZfHvZ7mBOxxc0sDZs=; SPC_T_IV=c3pUWkdLT1Vqc2JsaEZSQQ==; SPC_SI=kiRJaAAAAAB3NUw2SDZ5cFu8cgAAAAAAdWVValBlc2I=; _gcl_au=1.1.146666554.1750123214; _fbp=fb.2.1750123217080.766717869193498820; AMP_TOKEN=$NOT_FOUND; _ga=GA1.3.964029824.1750123219; _gid=GA1.3.1750018686.1750123222; SPC_CLIENTID=YVdlVXRWN3ptMVh5qzunfoarogcyseiz; SPC_EC=.TjU2ZE1XZFNXMm9DUkZNQmlMKmIILO/e+lr3oW73SAVqjW5QtNeWtzhgYzQZQr+uOCf38yNguHE+70gUdpf923BtLygINbR2+Cs+MNmFtz3HgdEQmm7ASGUHC2Rv4qHQjt/7Z9IBaHYdPIqOuG1sfib1HnmSb7buLM7/MfsDA+PkvZ9iOOoCmVkCUkERUlsx0YWWCZVMqqqQ7PNYhI+Ir6FfheCYE9iS/e5YE3YMiI5CXRDIKM8esYUjwI2MDJK2; SPC_ST=.VmhaVjRMRVE3cjFiNWxGNQq2Bc7FdsnynIxB9OcIaUbdfN9DtCzMB9lfpeX9Sb7ri0OBjkH/Q13THZrx73a0z3PgxV4GDZw25xqn8GMi3vpMWLathj7+iNt02PAMQwHGBzixHR6LF/2U9WbWNbPtD0dYe6hWy7qfmG9pVA42AJRfkNSRgdJRN77gF0qx0Dr3le4wZLJimCVu8/14uIpVET6Lfe2UtsurPM2wNmS+/fqBIRswfbyhlSXI/skRnukN; SPC_SC_SESSION=g6GSk4huTfC4bvaG9Esimye25SgXWaL5mxT+Dz/93A/UjVFgfX61pqOHhPBV6gbsVNfT3WyzbY/Xyz1ycjQ0rhFRXc9XI28l+T49QJdY+G1BQqxzOSrXAYdoRL0+MPM86oX+wX8+01jnTs9FIuBCr6ZtzJmmtaXWgwD0qokOMKDzEBUgniB9GBUUiVt0gvc46tFU1u+QofFEMyga7FTJ496AGNBETWai7hhbR7kmlpCauSqAM6nTpFr3K64xSV+XFKLOdHMQWGHpf0wYmOThhwQ==_1_1408398178; SPC_STK=Oe3bCAUrTcm/HKNP2tJd9+ZVIT4wAZOD3Me1f0/yq8Uqow0PCt6DvZVPzZ2knfQfGA/KSYxEm+Ul5fvBoBYXk1l8uxtK6CM7jI8HuVHm+57yz24hq5HL+mw38cd0sB0DHleue8uN2z8haeYRMgbEMu/0RH5E5/LSZX84G9f6U3h1kkJXXRml2z5ndk5zKtVXtgCs77IAAhz4PMH2ZAbZYSdMnxlo0G32d82Qrz8ZVbfBJo465rrj7UzIj3KEMFipkDt2QzHyjHabptE7+OHsXanfIWrdRc2+Gdrq2wCjMPigc7jLb2nnTgL7BuOiJw2tvz2DXNYeEiww7j3WlTxVVYgKz3X0f6M3qVY6AmGliETn30lxjI/U2YNSOYcphq6Dz7o/JHBMVWkgLn8t0d94Mnsp7I0i6as3SRAagq8GpYdhzTrCoFRWG3pxowiM7FZJzG4EPZomCGmAAObb/XUyo8KlQlHGpWPGAirrYymQQm37KHtKl/7yZPe6CNNeiVpo; SC_DFP=deyEdkdFYvAIlJyEJKMIFtKDYUPzFhUK; SPC_SI=kiRJaAAAAAB3NUw2SDZ5cFu8cgAAAAAAdWVValBlc2I=; _ga_SW6D8G0HXK=GS2.1.s1750123218$o1$g0$t1750123225$j53$l0$h0; SPC_CDS=af60da16-8937-4c93-9efa-9a8c4e3aad7c; SPC_CDS_CHAT=71b55a5f-1837-464e-aadb-31a9b831aa8a; _QPWSDCXHZQA=5ed50d6d-8a3f-46fb-a110-0297e4b62205; REC7iLP4Q=e5039adc-4505-4f55-8805-932d2c9b9fa0; _sapid=8d7f306369bd600b8822fc618b7d01d78c5f72a90ea0df2c09c59c7b; SPC_SEC_SI=v1-ckt3U1IwMmQzc2Q0WkRVaw3QHj8mT///HvkJbVi0E0LYyPinu8/s3dbxZRDb/cVIqryVZ9WOnsnZ6SFkWElUyWz2Ix9k3z6KEfVOPIR7dwA=; CTOKEN=7ayDl00TEfCPr9awdJtl4Q==; shopee_webUnique_ccd=jbqn1hJYwYNvuTlVw6yDJA==|5sM8X6Jh0nJ91vI1BmZ95KcbVR/cPL15AlZtKCEhh8jQpo8+a86XckK9uqdvDlicbJMHqZHNcxlkxMsZO5U=|gNIp82OmoahX1srv|08|3; ds=076b3e4137ea895fb3ecfe2d9b29e3db',
        'user-agent':'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/125.0.0.0 Safari/537.36'
    }
    res=requests.post('https://seller.shopee.co.id/api/pas/v1/report/get/',params=paras,json=data,headers=headers)
    print(res.json())


